/**
 * FavouriesDAO.java
 * Nov 18, 2013
 * Copyright: phongtdse60732@fpt.edu.vn
 */
package DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import DBUltilities.ConnectDB;
import DTO.DietDTO;
import DTO.MenuDTO;
import VMO.FavouriesMenuVMO;

/**
 * @author trandophong
 * @version 1.0
 */
public class FavouriesDAO {
	public void add(String username, int menuId, boolean isStart) {
		Connection conn = null;
		PreparedStatement preparedStatement = null;

		String sqlStatement = "INSERT INTO FAVOURIES_MENU"
				+ "(USERNAME, MENUID, STARTDAY) VALUES" + "(?,?,?)";
		try {
			conn = ConnectDB.getConnection();
			preparedStatement = conn.prepareStatement(sqlStatement);

			preparedStatement.setString(1, username);
			preparedStatement.setInt(2, menuId);
			if (isStart) {
				java.sql.Date sqlDate = new java.sql.Date(
						new java.util.Date().getTime());
				preparedStatement.setDate(3, sqlDate);
			} else {
				preparedStatement.setDate(3, null);
			}

			preparedStatement.executeUpdate();
		} catch (SQLException e) {
		} finally {
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}
		}
	}

	public void update(String username, int menuId) {
		Connection conn = null;
		PreparedStatement preparedStatement = null;

		String sqlStatement = "UPDATE SET STARTDAY = ? WHERE USERNAME = ? AND MENUID = ?";
		try {
			conn = ConnectDB.getConnection();
			preparedStatement = conn.prepareStatement(sqlStatement);

			java.sql.Date sqlDate = new java.sql.Date(
					new java.util.Date().getTime());
			preparedStatement.setDate(1, sqlDate);
			preparedStatement.setString(2, username);
			preparedStatement.setInt(3, menuId);

			preparedStatement.executeUpdate();
		} catch (SQLException e) {
		} finally {
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}
		}
	}

	public void delete(String username, int menuId) {
		Connection conn = null;
		PreparedStatement preparedStatement = null;

		String sqlStatement = "DELETE FROM FAVOURIES_MENU WHERE USERNAME = ? AND MENUID = ?";
		try {
			conn = ConnectDB.getConnection();
			preparedStatement = conn.prepareStatement(sqlStatement);

			preparedStatement.setString(1, username);
			preparedStatement.setInt(2, menuId);

			preparedStatement.executeUpdate();
		} catch (SQLException e) {
		} finally {
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}
		}
	}

	public boolean checkExist(String username, int menuId) {
		Connection conn = null;
		PreparedStatement preparedStatement = null;

		String sqlStatement = "SELECT * FROM FAVOURIES_MENU WHERE USERNAME = ? AND MENUID = ?";
		try {
			conn = ConnectDB.getConnection();
			preparedStatement = conn.prepareStatement(sqlStatement);

			preparedStatement.setString(1, username);
			preparedStatement.setInt(2, menuId);

			return preparedStatement.executeQuery().next();

		} catch (SQLException e) {
		} finally {
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}
		}
		return false;
	}

	public ArrayList<FavouriesMenuVMO> getMenuListByUser(String username) {
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		ArrayList<FavouriesMenuVMO> menuList = new ArrayList<FavouriesMenuVMO>();

		String sqlStatement = "SELECT MENU.*, STARTDAY FROM FAVOURIES_MENU, MENU WHERE USERNAME = ? AND MENUID = ID";
		try {
			conn = ConnectDB.getConnection();
			preparedStatement = conn.prepareStatement(sqlStatement);

			preparedStatement.setString(1, username);

			ResultSet rs = preparedStatement.executeQuery();
			int id;
			String name;
			String description;
			String imgPath;
			int calo;
			int numberday;
			int numbermeal;
			String strDate;
			long date;
			int day;

			while (rs.next()) {
				id = Integer.parseInt(rs.getObject(1) == null ? "0" : rs
						.getObject(1).toString());
				name = new String(rs.getObject(2) == null ? "" : rs
						.getObject(2).toString());
				description = new String(rs.getObject(3) == null ? "" : rs
						.getObject(3).toString());
				imgPath = new String(rs.getObject(4) == null ? "" : rs
						.getObject(4).toString());

				DietDTO diet = new DietDTO();
				diet.setId(Integer.parseInt(rs.getObject(5) == null ? "0" : rs
						.getObject(5).toString()));

				numberday = Integer.parseInt(rs.getObject(6) == null ? "0" : rs
						.getObject(6).toString());
				numbermeal = Integer.parseInt(rs.getObject(7) == null ? "0"
						: rs.getObject(7).toString());
				calo = Integer.parseInt(rs.getObject(8) == null ? "0" : rs
						.getObject(8).toString());
				strDate = (rs.getObject(9) == null ? "" : rs.getObject(9)
						.toString());
				SimpleDateFormat dateFormater = new SimpleDateFormat(
						"yyyy-MM-dd");
				if (!strDate.equals("")) {
					date = dateFormater.parse(strDate).getTime();
					long now = new Date().getTime();
					day = (int) Math.abs((now - date) / (1000 * 60 * 60 * 24)) + 1;
					if (day < 0) {
						day = 1;
					} else if (day > numberday && numberday != 0) {
						day = numberday;
					}
				} else {
					day = 1;
				}

				MenuDTO menu = new MenuDTO(id, diet, name, description,
						imgPath, calo, numberday, numbermeal);
				FavouriesMenuVMO fMenu = new FavouriesMenuVMO();
				fMenu.setMenu(menu);
				fMenu.setDay(day);
				menuList.add(fMenu);
			}
			return menuList;

		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ParseException e) {
		} finally {
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}
		}
		return menuList;
	}
}